package de.bib.blue.manage.finance.database;

import java.sql.BatchUpdateException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Timestamp;
import java.util.LinkedList;
import java.util.List;

public class ResourceDatabase {

	private static final String USER = "monas_user";

	private static final String PASSWORD = "monas_pwd";

	private static final String HOSTNAME = "monas_host";

	private static final String DB_NAME = "monas_dbname";

	private static final String DRIVER = "com.mysql.jdbc.Driver";

	// private static final String DRIVER = "org.postgresql.Driver";

	private static ResourceDatabase resource = null;

	private Connection connection = null;

	private boolean admin = false;

	private ResourceDatabase() throws Exception {
		String userName = System.getProperty(USER);
		String password = System.getProperty(PASSWORD);
		String hostName = System.getProperty(HOSTNAME);
		String databaseName = System.getProperty(DB_NAME);

		Class.forName(DRIVER);

		// String url = "jdbc:postgresql://" + hostName + "/" + databaseName;
		String url = "jdbc:mysql://" + hostName + "/" + databaseName;
		connection = DriverManager.getConnection(url, userName, password);
	}

	public static ResourceDatabase getInstance() throws Exception {
		if (resource == null) {
			resource = new ResourceDatabase();
		}
		return resource;
	}

	public boolean isAdmin() {
		return admin;
	}

	public void executeUpdate(String query) {
		Statement statement = null;

		try {
			statement = connection.createStatement();
			statement.executeUpdate(query);
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				System.out.println("Problem executing query : " + query + " : "
						+ e.getMessage());
			}
		}
	}

	public List<Object[]> executeQuery(String query) {
		ResultSet resultSet = null;
		PreparedStatement statement = null;
		LinkedList<Object[]> list = new LinkedList<Object[]>();
		try {
			statement = connection.prepareStatement(query);
			resultSet = statement.executeQuery();

			ResultSetMetaData metaData = resultSet.getMetaData();

			while (resultSet.next()) {
				Object[] fields = new Object[metaData.getColumnCount()];
				for (int i = 0; i < fields.length; i++) {
					fields[i] = resultSet.getObject(i + 1);
				}
				list.addLast(fields);
			}
		} catch (Exception e) {
		} finally {
			try {
				if (resultSet != null) {
					resultSet.close();
				}

				if (statement != null) {
					statement.close();
				}
			} catch (SQLException e) {
				System.out.println("Problem executing query : " + query + " : "
						+ e.getMessage());
			}
		}
		return list;
	}

	public void insertTable(List<Object[]> records) throws SQLException {
		String query = "INSERT INTO detailreportpengeluaran VALUES (?,?,?,?,?,?)";

		PreparedStatement statement = null;
		try {
			statement = connection.prepareStatement(query);
			for (Object[] fields : records) {
				for (int i = 0; i < fields.length; i++) {
					if (i == fields.length - 1) {
						statement.setInt(i + 1,
								Integer.valueOf(fields[i].toString()));
					} else {
						statement.setString(i + 1, fields[i].toString());
					}
				}
				statement.addBatch();
			}

			checkUpdateCounts(statement.executeBatch());
		} catch (BatchUpdateException e) {
			int[] updateCounts = e.getUpdateCounts();
			checkUpdateCounts(updateCounts);
			try {
				connection.rollback();
			} catch (Exception e2) {
				e.printStackTrace();
			}
		} finally {
			if (statement != null) {
				statement.close();
			}
		}

	}

	private void checkUpdateCounts(int[] updateCounts) {
		for (int i = 0; i < updateCounts.length; i++) {
			if (updateCounts[i] >= 0) {
				System.out.println("Successfully executed; updateCount="
						+ updateCounts[i]);
			} else if (updateCounts[i] == Statement.SUCCESS_NO_INFO) {
				System.out
						.println("Successfully executed; updateCount=Statement.SUCCESS_NO_INFO");
			} else if (updateCounts[i] == Statement.EXECUTE_FAILED) {
				System.out
						.println("Failed to execute; updateCount=Statement.EXECUTE_FAILED");
			}
		}
	}

	public boolean login(String userName, String password) {
		String query = "SELECT * FROM admin WHERE Nama_User = '" + userName
				+ "' AND Password_User = '" + password + "'";
		List<Object[]> list = executeQuery(query);
		if (list.size() > 0) {
			for (Object[] values : list) {
				admin = values[3].equals("Admin");
				return true;
			}
		}
		return false;
	}

	public List<Object[]> getAllUser() {
		String query = "SELECT Nama_User, Password_User, Jabatan, User_type, Remark FROM admin ORDER BY 1";
		return executeQuery(query);
	}

	public void addUser(String username, String password, boolean isAdmin,
			String remark) {
		String type = "Admin";
		if (!isAdmin) {
			type = "Common";
		}
		String query = "INSERT INTO admin (Nama_User, Password_User, Jabatan,User_type, "
				+ " Remark) values ('"
				+ username
				+ "','"
				+ password
				+ "','"
				+ type + "','enable','" + remark + "')";
		executeUpdate(query);
	}

	public void editUser(String username, String password, String remark) {
		String query = "UPDATE admin SET Password_User='" + password
				+ "', remark='" + remark + "' WHERE Nama_User='" + username
				+ "'";
		executeUpdate(query);
	}

	public void deleteUser(String username) {
		String query = "DELETE FROM admin WHERE Nama_User='" + username + "'";
		executeUpdate(query);
	}

	public List<Object[]> getSummary() {
		return executeQuery("SELECT dr.Bulan_Transaksi As Bulan, SUM(dr.Jumlah_Transaksi) AS Total FROM DetailReportPengeluaran dr GROUP BY dr.Bulan_Transaksi");
	}

	public List<Object[]> getPercentageByFirstMonth() {
		return executeQuery("SELECT p.Kode,p.Persen FROM(SELECT d1.Bulan as Bulan,d1.Kode as Kode,FLOOR((d1.Total / d2.Total * 100)) AS Persen FROM (SELECT dr.Bulan_Transaksi AS Bulan, dr.Kode_Transaksi AS Kode,SUM(dr.Jumlah_Transaksi) AS Total FROM DetailReportPengeluaran dr GROUP BY dr.Bulan_Transaksi,dr.Kode_Transaksi) d1,(SELECT dr.Bulan_Transaksi AS Bulan,SUM(dr.Jumlah_Transaksi) AS Total FROM DetailReportPengeluaran dr GROUP BY dr.Bulan_Transaksi)d2 WHERE	d1.Bulan = d2.Bulan) p WHERE p.Bulan like 'Jan%' ORDER BY p.Kode");
	}

	public List<Object[]> getPercentageByMonth(String month) {
		return executeQuery("SELECT p.Kode,p.Persen FROM(SELECT d1.Bulan as Bulan,d1.Kode as Kode,FLOOR((d1.Total / d2.Total * 100)) AS Persen FROM (SELECT dr.Bulan_Transaksi AS Bulan, dr.Kode_Transaksi AS Kode,SUM(dr.Jumlah_Transaksi) AS Total FROM DetailReportPengeluaran dr GROUP BY dr.Bulan_Transaksi,dr.Kode_Transaksi) d1,(SELECT dr.Bulan_Transaksi AS Bulan,SUM(dr.Jumlah_Transaksi) AS Total FROM DetailReportPengeluaran dr GROUP BY dr.Bulan_Transaksi)d2 WHERE	d1.Bulan = d2.Bulan) p WHERE p.Bulan like '"
				+ month + "%' ORDER BY p.Kode");
	}

	public List<Object[]> getDetailByFirstMonth() {
		return executeQuery("select Tgl_Transaksi,Bulan_Transaksi,Kode_Transaksi,Jumlah_Transaksi,Keterangan from detailreportpengeluaran where Bulan_Transaksi like 'Jan%' Order By Tgl_Transaksi ");
	}

	public List<Object[]> getDetailByMonth(String month) {
		return executeQuery("select Tgl_Transaksi,Bulan_Transaksi,Kode_Transaksi,Jumlah_Transaksi,Keterangan from detailreportpengeluaran where Bulan_Transaksi like '"
				+ month + "%' Order By Tgl_Transaksi ");
	}

	public List<Object[]> getDetailBySearch(String param) {
		return executeQuery("select Tgl_Transaksi,Bulan_Transaksi,Kode_Transaksi,Jumlah_Transaksi,Keterangan from detailreportpengeluaran where "
				+ param + " Order By Tgl_Transaksi ");
	}

	/**
	 * Method untuk menghentikan koneksi database.
	 */
	public void close() {
		if (connection != null) {
			try {
				connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			}
		}
	}

}
